﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;

namespace meituan
{
    class Excel
    {
        public static DataTable ExcelToTable(string file)
        {
            DataTable dt = new DataTable();
            IWorkbook workbook;
            string fileExt = Path.GetExtension(file).ToLower();
            try
            {
                using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
                    if (workbook == null) { return null; }
                    ISheet sheet = workbook.GetSheetAt(0);

                    //表头  
                    IRow header = sheet.GetRow(sheet.FirstRowNum);
                    List<int> columns = new List<int>();
                    for (int i = 0; i < header.LastCellNum; i++)
                    {
                        object obj = GetValueType(header.GetCell(i));
                        if (obj == null || obj.ToString() == string.Empty)
                        {
                            dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
                        }
                        else
                            dt.Columns.Add(new DataColumn(obj.ToString()));
                        columns.Add(i);
                    }
                    //数据  
                    for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                    {
                        DataRow dr = dt.NewRow();
                        bool hasValue = false;
                        foreach (int j in columns)
                        {
                            dr[j] = GetValueType(sheet.GetRow(i).GetCell(j));
                            if (dr[j] != null && dr[j].ToString() != string.Empty)
                            {
                                hasValue = true;
                            }
                        }
                        if (hasValue)
                        {
                            dt.Rows.Add(dr);
                        }
                    }
                }
            }
            catch (IOException ex)
            {
                /*String str = "";
                str += ex.Message + "\n";//异常消息
                str += ex.StackTrace + "\n";//提示出错位置，不会定位到方法内部去*/
                String str = ex.ToString() + "\n";//将方法内部和外部所有出错的位置提示出来
                System.Windows.Forms.MessageBox.Show(str);
            }

            return dt;
        }

        private static object GetValueType(ICell cell)
        {
            if (cell == null)
                return null;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return null;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Numeric:
                    return cell.NumericCellValue;
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Error:
                    return cell.ErrorCellValue;
                case CellType.Formula:
                default:
                    return "=" + cell.CellFormula;
            }
        }

        public static void WriteToCSV(string dir, string filename, string strdata, bool flag = false)
        {
            if (strdata == "")
            {
                return;
            }
            
            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            string filepath = dir + "\\" + filename + ".csv";
            if (!File.Exists(filepath))
            {
                if (flag == false)
                {
                    strdata = "ID,标题,行业分类,地址,区域,价格,评分,图片\n" + strdata;
                }
                else
                {
                    strdata = "ID,昵称,头像,评论,回复,产品图片,产品描述,时间\n" + strdata;
                }
            }
            try
            {
                FileStream fs = new FileStream(filepath, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);
                sw.Write(strdata);
                sw.Flush();
                sw.Close();
            }
            catch (System.IO.IOException ex)
            {
                Console.WriteLine(ex.Message);
                return;
            }

        }

        public static DataTable ReadDataFromCsv(string file)
        {
            DataTable dt = null;

            if (File.Exists(file))
            {
                dt = new DataTable();
                try
                {
                    FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read);
                    StreamReader sr = new StreamReader(fs, System.Text.Encoding.UTF8);
                    string head = sr.ReadLine();
                    string[] headNames = head.Split(',');
                    for (int i = 0; i < headNames.Length; i++)
                    {
                        dt.Columns.Add(headNames[i], typeof(string));
                    }
                    while (!sr.EndOfStream)
                    {
                        string lineStr = sr.ReadLine();
                        if (lineStr == null || lineStr.Length == 0)
                            continue;
                        string[] values = lineStr.Split(',');
                        DataRow dr = dt.NewRow();
                        for (int i = 0; i < values.Length; i++)
                        {
                            dr[i] = values[i];
                        }
                        dt.Rows.Add(dr);
                    }
                    fs.Close();
                    sr.Close();
                }
                catch (System.IO.IOException)
                {
                    System.Windows.Forms.MessageBox.Show("文件被占用");
                }
               

            }
            return dt;
        }

    }
}
